
ALTER    proc sp_load_final_inventory_by_partnumber_and_lineitem_drop_shipment
(@mfgPartNumber varchar(50), @locationId int )
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)
declare @results Table(	Lot_Number varchar(20),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Step_Name varchar(20))

--declare @temp_cslit_id int
declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(20)
declare @temp_location varchar(20)
--declare @temp_step_name varchar(20)
declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
--declare @partNumber varchar(50)
--declare @max_time_stamp datetime

set @pfcursor_deallocate_status = 0
select @temp_process_step_id = ProcessStep_ID from Product where 
Part_Number =  @mfgPartNumber and p_marketing_Part_Number is not null

declare pfCursor CURSOR for
select *from fn_get_process_flow_by_id(@temp_process_step_id)
open pfCursor
	fetch next from pfCursor
	into @process_step_id, @process_type, @process_type_name
	while @@fetch_status = 0
	begin
		print '@Process_type_name = '+ @process_type_name
		if @process_type_name = 'Finished Goods'
		begin
			set @tempCursor = CURSOR for
			--select lt.Lot_ID, lt.Lot_Number, lt.Location_ID from Lot_Table lt where ProcessStep_ID = @process_step_id and ( Lot_Current_Status = 'Running' or Lot_Current_Status = 'Close' ) order by location_id old query commented after discussion with Mallik 5 Jan 2004
			select lt.Lot_ID, lt.Lot_Number, lt.Location_ID from Lot_Table lt where ProcessStep_ID = @process_step_id and location_id = @locationId and ( Lot_Current_Status = 'Running' ) order by location_id
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id
			while @@fetch_status = 0
			begin
				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
				--else
				--begin
				insert into @results( Lot_Number, Lot_ID, Location_ID, Location, Step_Name ) values( @temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name)
				--end
				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id
			end
			close @tempCursor 
			deallocate @tempCursor
			set @pfcursor_deallocate_status = 1
		end


		fetch next from pfCursor
		into @process_step_id, @process_type, @process_type_name
	end

	if @pfcursor_deallocate_status = 1
	begin
		--deallocate @tempCursor
		close pfCursor
		deallocate pfCursor
		select *from @results
		return
	end
close pfCursor
deallocate pfCursor


